﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.IO;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;

namespace cls.u_Models
{
    public partial class ShowSaleplan : System.Web.UI.Page
    {
        static String OrgType = "";
        static String OrgCode = "";
        static String UserName = "";
        static String UserInOrgId = "";
        static String UserInOrgCode = "";
        static Boolean UserInZB = false;
        static String LastInsDatetime = "";

        protected void Page_Load(object sender, EventArgs e)
        {
            UserName = User.Identity.Name;
            OrgType = Request.QueryString["OrgType"];
            OrgCode = Request.QueryString["OrgCode"];

            if (UserName == null || UserName.Equals(""))
            {
                Response.Redirect("SaleplanMgr.aspx");
            }
            else if (OrgType == null || OrgCode == null)
            {
                Response.Redirect("SaleplanMgr.aspx");
            }
            else if (OrgType.Equals("") || OrgCode.Equals(""))
            {
                Response.Redirect("SaleplanMgr.aspx");
            }
            else if (OrgType.Equals("ZB"))
            {
                //GridView_BodyDataBind(OrgType, "ZB", GetLastInsDatetime());
            }
            else if (OrgType.Equals("Shop"))
            {
                //GridView_ShowPlanHead.Visible = true;
                //GridView_ShowPlanHead.DataSource = GridView_DataSet(OrgType, OrgCode, GetLastInsDatetime());
                //GridView_ShowPlanHead.DataSource = GridView_DataSet(OrgType, OrgCode, "2014-08-11 17:44:12.000");

                //GridView_BodyDataBind(OrgType, OrgCode, LastInsDatetime);
            }

            UserInOrgId = GetOrgIdByUserName(UserName);
            UserInOrgCode = GetOrgCodeByUserName(UserName);
            LastInsDatetime = GetLastInsDatetime();

            if (UserInOrgCode.Trim().Equals("ZB"))
            {
                UserInZB = true;
            }
            else
            {
                UserInZB = false;
            }

            if (!Page.IsPostBack)
            {
                DropDownList_ShowOrgNames();
                DropDownList_SelectMonth_AddItems();
            }
        }

        protected String GetOrgCodeByUserName(String UserName)
        {
            String OrgCode = "";

            SqlConnection SqlConnection = new SqlConnection("Data Source=192.168.0.241;Initial Catalog=cls_forms_db;Persist Security Info=True;User ID=sa;Password=My.SA");
            SqlConnection.Open();

            SqlCommand SelectCommand = new SqlCommand("SELECT OrgCode FROM u_cls_Org WHERE Id = (SELECT OrgId FROM u_cls_UserInOrg WHERE UserName = @UserName)", SqlConnection);

            SelectCommand.Parameters.Add("@UserName", SqlDbType.NVarChar, 4);
            SelectCommand.Parameters["@UserName"].Value = UserName;

            SqlDataReader OrgCodeReader = SelectCommand.ExecuteReader();

            while (OrgCodeReader.Read())
            {
                //还是分开取得比较好，后面还有单独用到OrgCode的地方......
                //OrgCode = ResReader["OrgCode"].ToString() + "-" + ResReader["OrgName"].ToString();  
                OrgCode = OrgCodeReader["OrgCode"].ToString().Trim();
            }

            OrgCodeReader.Close();
            SqlConnection.Close();

            return OrgCode;
        }

        protected String GetOrgIdByUserName(String UserName)
        {
            String OrgId = "";

            SqlConnection SqlConnection = new SqlConnection("Data Source=192.168.0.241;Initial Catalog=cls_forms_db;Persist Security Info=True;User ID=sa;Password=My.SA");
            SqlConnection.Open();

            SqlCommand SelectCommand = new SqlCommand("SELECT [OrgId] FROM [dbo].[u_cls_UserInOrg] WHERE [UserName] = @UserName", SqlConnection);

            SelectCommand.Parameters.Add("@UserName", SqlDbType.NVarChar, 4);
            SelectCommand.Parameters["@UserName"].Value = UserName;

            SqlDataReader OrgIdReader = SelectCommand.ExecuteReader();

            while (OrgIdReader.Read())
            {
                OrgId = OrgIdReader["OrgId"].ToString().ToUpper();
            }

            OrgIdReader.Close();
            SelectCommand.Dispose();
            SqlConnection.Close();

            return OrgId;
        }

        protected String GetLastInsDatetime()
        {
            //String LastInsDatetime = "";
            SqlConnection SqlConnection = new SqlConnection("Data Source=192.168.0.241;Initial Catalog=cls_forms_db;Persist Security Info=True;User ID=sa;Password=My.SA");
            SqlConnection.Open();

            SqlCommand SelectCommand = new SqlCommand("SELECT TOP(1) LastInsDatetime FROM [dbo].[u_cls_LastInsDatetime] WHERE ApplicationName = 'Saleplan' ORDER BY LastInsDatetime DESC", SqlConnection);
            SqlDataReader LastInsDatetimeReader = SelectCommand.ExecuteReader();

            //酱紫写法系错误嘀，乖乖的用While循环吧......
            //if(LastInsDatetimeReader.HasRows)
            //{
            //    LastInsDatetime = LastInsDatetimeReader["LastInsDatetime"].ToString();
            //}
            while (LastInsDatetimeReader.Read())
            {
                LastInsDatetime = LastInsDatetimeReader["LastInsDatetime"].ToString();
            }

            LastInsDatetimeReader.Close();
            SqlConnection.Close();

            return LastInsDatetime;
        }

        protected void DropDownList_SelectMonth_AddItems()
        {
            SqlConnection SqlConnection = new SqlConnection("Data Source=192.168.0.241;Initial Catalog=cls_forms_db;Persist Security Info=True;User ID=sa;Password=My.SA");
            SqlConnection.Open();

            SqlCommand SelectCommand = new SqlCommand("SELECT DISTINCT CONVERT(nvarchar, YEAR(PlanYearMonth))+'-'+ CONVERT(nvarchar, MONTH(PlanYearMonth)) AS YearMonth FROM [dbo].[u_cls_Saleplan_Table] WHERE PlanYearMonth IS NOT NULL ORDER BY YearMonth DESC", SqlConnection);

            SqlDataReader YearMonthReader = SelectCommand.ExecuteReader();

            while (YearMonthReader.Read())
            {
                DropDownList_SelectPlanMonth.Items.Add(new ListItem(YearMonthReader["YearMonth"].ToString(), YearMonthReader["YearMonth"].ToString() + "-01"));                
            }

            //处理无满足条件
            if (YearMonthReader.HasRows)
            {                
                DropDownList_SelectPlanMonth.Visible = true;                
            }
            else
            {                
                DropDownList_SelectPlanMonth.Visible = false;               
            }
            
            YearMonthReader.Close();
            SelectCommand.Dispose();
            SqlConnection.Close();
        }

        protected void DropDownList_ShowOrgNames()
        {
            DropDownList_SelectOrgCode.Items.Clear();

            String SelectCommandString = "";

            if (OrgCode.Trim().Equals("ZB"))
            {
                DropDownList_SelectOrgCode.Items.Add(new ListItem("全部", "all"));
                SelectCommandString = "SELECT OrgCode,OrgName FROM [dbo].[u_cls_Org] ORDER BY OrgCode";
            }
            else
            {
                SelectCommandString = "SELECT OrgCode,OrgName FROM [dbo].[u_cls_Org] WHERE Id = '" + UserInOrgId + "' ORDER BY OrgCode";
            }

            SqlConnection SqlConnection = new SqlConnection("Data Source=192.168.0.241;Initial Catalog=cls_forms_db;Persist Security Info=True;User ID=sa;Password=My.SA");
            SqlConnection.Open();

            SqlCommand SelectCommand = new SqlCommand(SelectCommandString, SqlConnection);
            SqlDataReader OrgCodeReader = SelectCommand.ExecuteReader();

            while (OrgCodeReader.Read())
            {
                DropDownList_SelectOrgCode.Items.Add(new ListItem(OrgCodeReader["OrgCode"].ToString() + "-" + OrgCodeReader["OrgName"].ToString(), OrgCodeReader["OrgCode"].ToString()));
            }

            OrgCodeReader.Close();
            SelectCommand.Dispose();
            SqlConnection.Close();
        }

        protected DataSet GridView_DataSet(String OrgType, String OrgCode, String LastImportDatetime)
        {
            //计划月份条件
            String SelectedMonth = DropDownList_SelectPlanMonth.SelectedValue;

            DataSet GridViewDataSet = new DataSet();

            SqlConnection SqlConnection = new SqlConnection("Data Source=192.168.0.241;Initial Catalog=cls_forms_db;Persist Security Info=True;User ID=sa;Password=My.SA");
            SqlConnection.Open();

            //SQL Server中CONVERT将nvarchar到Date参考
            //http://msdn.microsoft.com/zh-CN/library/ms187928.aspx
            //http://blog.163.com/zhouchunping_99/blog/static/783799882011723451614/
            //
            //RollUp语法参考
            //http://www.2cto.com/database/201210/163455.html
            //http://www.cnblogs.com/rippleyong/archive/2004/11/18/65143.html
            //http://www.cnblogs.com/yiway/archive/2007/11/12/957281.html
            //http://blog.csdn.net/a9529lty/article/details/5646612
            //http://blog.csdn.net/nanchangniat/article/details/7923144
            String SelectCommandStr = "SELECT [id],[OrgType],[OrgCode],[OrgName],[OrgPlan],CONVERT(nvarchar(7),PlanYearMonth,126) AS [PlanYearMonth],[WeekOne_ShopPlan],[WeekOne_ShopConf],[WeekTwo_ShopPlan],[WeekTwo_ShopConf],[WeekThree_ShopPlan],[WeekThree_ShopConf],[WeekFour_ShopPlan],[WeekFour_ShopConf],[ImportDatetime],[ImportUserName],[InsDatetime],[InsUserName],[SUM_ShopPlan],[SUM_ShopConf],[WeekDateRemark] FROM [dbo].[u_cls_Saleplan_Table] WHERE [ImportDatetime]='" + LastImportDatetime + "' AND PlanYearMonth BETWEEN (SELECT DATEADD(MONTH, DATEDIFF(MONTH,0,'" + SelectedMonth + "'), 0)) AND (SELECT DATEADD(ms,-1,DATEADD(mm, DATEDIFF(m,0,'" + SelectedMonth + "')+1, 0)))";

            if (OrgType.Equals("ZB"))
            {
                if (OrgCode.Equals("all"))
                { }
                else
                {
                    SelectCommandStr += " AND OrgCode='" + OrgCode + "'";
                }
            }
            else
            {
                SelectCommandStr += " AND OrgCode='" + OrgCode + "'";
            }

            SelectCommandStr += " ORDER BY OrgCode";

            SqlDataAdapter dataAdapter = new SqlDataAdapter(SelectCommandStr, SqlConnection);
            dataAdapter.Fill(GridViewDataSet);

            dataAdapter.Dispose();
            SqlConnection.Close();

            GridViewDataSet.Dispose();

            return GridViewDataSet;
        }

        //protected void GridView_HeadDataBind(String OrgType, String OrgCode, String LastImportDatetime)
        //{
        //    GridView_ShowPlanHead.AutoGenerateColumns = false;
        //    GridView_ShowPlanHead.Columns.Clear();
        //    GridView_ShowPlanHead.DataSource = GridView_DataSet(OrgType, OrgCode, LastImportDatetime);
        //    GridView_ShowPlanHead.DataKeyNames = new string[] { "id" };

        //    BoundField bField;

        //    bField = new BoundField();
        //    bField.DataField = "id";
        //    bField.HeaderText = "编号";
        //    bField.ReadOnly = true;
        //    bField.Visible = false;
        //    GridView_ShowPlanHead.Columns.Add(bField);

        //    //bField = new BoundField();
        //    //bField.DataField = "OrgType";
        //    //bField.HeaderText = "组织类型";
        //    //bField.ReadOnly = true;
        //    //bField.HeaderStyle.HorizontalAlign = HorizontalAlign.Center;
        //    //bField.ItemStyle.HorizontalAlign = HorizontalAlign.Center;
        //    //GridView_ShowPlanHead.Columns.Add(bField);

        //    //bField = new BoundField();
        //    //bField.DataField = "OrgCode";
        //    //bField.HeaderText = "组织编码";
        //    //bField.ReadOnly = true;
        //    //bField.HeaderStyle.HorizontalAlign = HorizontalAlign.Center;
        //    //bField.ItemStyle.HorizontalAlign = HorizontalAlign.Center;
        //    //GridView_ShowPlanHead.Columns.Add(bField);

        //    //bField = new BoundField();
        //    //bField.DataField = "OrgName";
        //    //bField.HeaderText = "组织名称";
        //    //bField.ReadOnly = true;
        //    //bField.HeaderStyle.HorizontalAlign = HorizontalAlign.Center;
        //    //bField.ItemStyle.HorizontalAlign = HorizontalAlign.Center;
        //    //GridView_ShowPlanHead.Columns.Add(bField);

        //    //bField = new BoundField();
        //    //bField.DataField = "OrgName";
        //    //bField.HeaderText = "组织名称";
        //    //bField.ReadOnly = true;
        //    //bField.HeaderStyle.HorizontalAlign = HorizontalAlign.Center;
        //    //bField.ItemStyle.HorizontalAlign = HorizontalAlign.Center;
        //    //GridView_ShowPlanHead.Columns.Add(bField);

        //    bField = new BoundField();
        //    bField.DataField = "PlanYearMonth";
        //    bField.HeaderText = "计划月份";
        //    bField.ReadOnly = true;
        //    bField.HeaderStyle.HorizontalAlign = HorizontalAlign.Center;
        //    bField.HeaderStyle.Font.Size = 8;            
        //    bField.ItemStyle.HorizontalAlign = HorizontalAlign.Center;
        //    GridView_ShowPlanHead.Columns.Add(bField);

        //    bField = new BoundField();
        //    bField.DataField = "WeekOne_BgDate";
        //    bField.HeaderText = "第一周开始日期";
        //    bField.ReadOnly = true;
        //    bField.HeaderStyle.HorizontalAlign = HorizontalAlign.Center;
        //    bField.HeaderStyle.Font.Size = 8;
        //    bField.ItemStyle.HorizontalAlign = HorizontalAlign.Center;
        //    GridView_ShowPlanHead.Columns.Add(bField);

        //    bField = new BoundField();
        //    bField.DataField = "WeekOne_EdDate";
        //    bField.HeaderText = "第一周结束日期";
        //    bField.ReadOnly = true;
        //    bField.HeaderStyle.HorizontalAlign = HorizontalAlign.Center;
        //    bField.HeaderStyle.Font.Size = 8;
        //    bField.ItemStyle.HorizontalAlign = HorizontalAlign.Center;
        //    GridView_ShowPlanHead.Columns.Add(bField);

        //    bField = new BoundField();
        //    bField.DataField = "WeekOne_ShopPlan";
        //    bField.HeaderText = "第一周计划金额";
        //    bField.ReadOnly = true;
        //    bField.HeaderStyle.HorizontalAlign = HorizontalAlign.Center;
        //    bField.HeaderStyle.Font.Size = 8;
        //    bField.ItemStyle.HorizontalAlign = HorizontalAlign.Center;
        //    GridView_ShowPlanHead.Columns.Add(bField);

        //    bField = new BoundField();
        //    bField.DataField = "WeekTwo_BgDate";
        //    bField.HeaderText = "第二周开始日期";
        //    bField.ReadOnly = true;
        //    bField.HeaderStyle.HorizontalAlign = HorizontalAlign.Center;
        //    bField.HeaderStyle.Font.Size = 8;
        //    bField.ItemStyle.HorizontalAlign = HorizontalAlign.Center;
        //    GridView_ShowPlanHead.Columns.Add(bField);

        //    bField = new BoundField();
        //    bField.DataField = "WeekTwo_EdDate";
        //    bField.HeaderText = "第二周结束日期";
        //    bField.ReadOnly = true;
        //    bField.HeaderStyle.HorizontalAlign = HorizontalAlign.Center;
        //    bField.HeaderStyle.Font.Size = 8;
        //    bField.ItemStyle.HorizontalAlign = HorizontalAlign.Center;
        //    GridView_ShowPlanHead.Columns.Add(bField);

        //    bField = new BoundField();
        //    bField.DataField = "WeekTwo_ShopPlan";
        //    bField.HeaderText = "第二周计划金额";
        //    bField.ReadOnly = true;
        //    bField.HeaderStyle.HorizontalAlign = HorizontalAlign.Center;
        //    bField.HeaderStyle.Font.Size = 8;
        //    bField.ItemStyle.HorizontalAlign = HorizontalAlign.Center;
        //    GridView_ShowPlanHead.Columns.Add(bField);

        //    bField = new BoundField();
        //    bField.DataField = "WeekThree_BgDate";
        //    bField.HeaderText = "第三周开始日期";
        //    bField.ReadOnly = true;
        //    bField.HeaderStyle.HorizontalAlign = HorizontalAlign.Center;
        //    bField.HeaderStyle.Font.Size = 8;
        //    bField.ItemStyle.HorizontalAlign = HorizontalAlign.Center;
        //    GridView_ShowPlanHead.Columns.Add(bField);

        //    bField = new BoundField();
        //    bField.DataField = "WeekThree_EdDate";
        //    bField.HeaderText = "第三周结束日期";
        //    bField.ReadOnly = true;
        //    bField.HeaderStyle.HorizontalAlign = HorizontalAlign.Center;
        //    bField.HeaderStyle.Font.Size = 8;
        //    bField.ItemStyle.HorizontalAlign = HorizontalAlign.Center;
        //    GridView_ShowPlanHead.Columns.Add(bField);

        //    bField = new BoundField();
        //    bField.DataField = "WeekThree_ShopPlan";
        //    bField.HeaderText = "第三周计划金额";
        //    bField.ReadOnly = true;
        //    bField.HeaderStyle.HorizontalAlign = HorizontalAlign.Center;
        //    bField.HeaderStyle.Font.Size = 8;
        //    bField.ItemStyle.HorizontalAlign = HorizontalAlign.Center;
        //    GridView_ShowPlanHead.Columns.Add(bField);

        //    bField = new BoundField();
        //    bField.DataField = "WeekFour_BgDate";
        //    bField.HeaderText = "第四周开始日期";
        //    bField.ReadOnly = true;
        //    bField.HeaderStyle.HorizontalAlign = HorizontalAlign.Center;
        //    bField.HeaderStyle.Font.Size = 8;
        //    bField.ItemStyle.HorizontalAlign = HorizontalAlign.Center;
        //    GridView_ShowPlanHead.Columns.Add(bField);

        //    bField = new BoundField();
        //    bField.DataField = "WeekFour_EdDate";
        //    bField.HeaderText = "第四周结束日期";
        //    bField.ReadOnly = true;
        //    bField.HeaderStyle.HorizontalAlign = HorizontalAlign.Center;
        //    bField.HeaderStyle.Font.Size = 8;
        //    bField.ItemStyle.HorizontalAlign = HorizontalAlign.Center;
        //    GridView_ShowPlanHead.Columns.Add(bField);

        //    bField = new BoundField();
        //    bField.DataField = "WeekFour_ShopPlan";
        //    bField.HeaderText = "第四周计划金额";
        //    bField.ReadOnly = true;
        //    bField.HeaderStyle.HorizontalAlign = HorizontalAlign.Center;
        //    bField.HeaderStyle.Font.Size = 8;
        //    bField.ItemStyle.HorizontalAlign = HorizontalAlign.Center;
        //    GridView_ShowPlanHead.Columns.Add(bField);

        //    bField = new BoundField();
        //    bField.DataField = "SUM_ShopPlan";
        //    bField.HeaderText = "计划金额合计";
        //    bField.ReadOnly = true;
        //    bField.HeaderStyle.HorizontalAlign = HorizontalAlign.Center;
        //    bField.HeaderStyle.Font.Size = 8;
        //    bField.ItemStyle.HorizontalAlign = HorizontalAlign.Center;
        //    GridView_ShowPlanHead.Columns.Add(bField);

        //    GridView_ShowPlanHead.DataBind();
        //}

        protected void GridView_BodyDataBind(String OrgType, String OrgCode, String LastImportDatetime)
        {
            //计划月份条件
            //SelectedMonth = DropDownList_SelectPlanMonth.SelectedValue;

            GridView_ShowPlanBody.AutoGenerateColumns = false;
            GridView_ShowPlanBody.Columns.Clear();
            GridView_ShowPlanBody.DataSource = GridView_DataSet(OrgType, OrgCode, LastImportDatetime);
            GridView_ShowPlanBody.DataKeyNames = new string[] { "id" };

            BoundField bField;

            bField = new BoundField();
            bField.DataField = "id";
            bField.HeaderText = "编号";
            bField.ReadOnly = true;
            bField.Visible = false;
            GridView_ShowPlanBody.Columns.Add(bField);

            //bField = new BoundField();
            //bField.DataField = "OrgType";
            //bField.HeaderText = "组织类型";
            //bField.ReadOnly = true;
            //bField.HeaderStyle.HorizontalAlign = HorizontalAlign.Center;
            //bField.ItemStyle.HorizontalAlign = HorizontalAlign.Center;
            //GridView_ShowPlanBody.Columns.Add(bField);

            bField = new BoundField();
            bField.DataField = "PlanYearMonth";
            bField.HeaderText = "计划月份";
            bField.ReadOnly = true;
            bField.HeaderStyle.HorizontalAlign = HorizontalAlign.Center;
            bField.HeaderStyle.Font.Size = 8;
            bField.ItemStyle.HorizontalAlign = HorizontalAlign.Center;
            GridView_ShowPlanBody.Columns.Add(bField);

            bField = new BoundField();
            bField.DataField = "OrgCode";
            bField.HeaderText = "组织编码";
            bField.ReadOnly = true;
            bField.HeaderStyle.HorizontalAlign = HorizontalAlign.Center;
            bField.ItemStyle.HorizontalAlign = HorizontalAlign.Center;
            GridView_ShowPlanBody.Columns.Add(bField);

            bField = new BoundField();
            bField.DataField = "OrgName";
            bField.HeaderText = "组织名称";
            bField.ReadOnly = true;
            bField.HeaderStyle.HorizontalAlign = HorizontalAlign.Center;
            bField.ItemStyle.HorizontalAlign = HorizontalAlign.Center;
            GridView_ShowPlanBody.Columns.Add(bField);

            //bField = new BoundField();
            //bField.DataField = "OrgName";
            //bField.HeaderText = "组织名称";
            //bField.ReadOnly = true;
            //bField.HeaderStyle.HorizontalAlign = HorizontalAlign.Center;
            //bField.ItemStyle.HorizontalAlign = HorizontalAlign.Center;
            //GridView_ShowPlanBody.Columns.Add(bField);            

            //bField = new BoundField();
            //bField.DataField = "WeekOne_BgDate";
            //bField.HeaderText = "第一周开始日期";
            //bField.ReadOnly = true;
            //bField.HeaderStyle.HorizontalAlign = HorizontalAlign.Center;
            //bField.HeaderStyle.Font.Size = 8;
            //bField.ItemStyle.HorizontalAlign = HorizontalAlign.Center;
            //GridView_ShowPlanBody.Columns.Add(bField);

            //bField = new BoundField();
            //bField.DataField = "WeekOne_EdDate";
            //bField.HeaderText = "第一周结束日期";
            //bField.ReadOnly = true;
            //bField.HeaderStyle.HorizontalAlign = HorizontalAlign.Center;
            //bField.HeaderStyle.Font.Size = 8;
            //bField.ItemStyle.HorizontalAlign = HorizontalAlign.Center;
            //GridView_ShowPlanBody.Columns.Add(bField);

            bField = new BoundField();
            bField.DataField = "OrgPlan";
            bField.HtmlEncode = false;
            bField.HeaderText = "计划目标<br />（元）";            
            bField.ReadOnly = true;
            bField.HeaderStyle.HorizontalAlign = HorizontalAlign.Center;
            bField.HeaderStyle.Font.Size = 8;
            bField.ItemStyle.HorizontalAlign = HorizontalAlign.Right;
            GridView_ShowPlanBody.Columns.Add(bField);

            //[5]
            bField = new BoundField();
            bField.DataField = "WeekOne_ShopPlan";
            bField.HtmlEncode = false;
            //bField.HeaderText = "第一周计划目标（万元）";
            //bField.HeaderText = HttpUtility.HtmlEncode("第一周计划目标<br />（万元）");
            //bField.HeaderText = HttpUtility.HtmlDecode("第一周计划目标<br />（万元）");
            bField.HeaderText = "第一旬计划目标<br />（元）";
            bField.ReadOnly = UserInZB;
            bField.HeaderStyle.HorizontalAlign = HorizontalAlign.Center;
            bField.HeaderStyle.Font.Size = 8;
            bField.ItemStyle.HorizontalAlign = HorizontalAlign.Right;
            GridView_ShowPlanBody.Columns.Add(bField);

            //bField = new BoundField();
            //bField.DataField = "WeekTwo_BgDate";
            //bField.HeaderText = "第二周开始日期";
            //bField.ReadOnly = true;
            //bField.HeaderStyle.HorizontalAlign = HorizontalAlign.Center;
            //bField.HeaderStyle.Font.Size = 8;
            //bField.ItemStyle.HorizontalAlign = HorizontalAlign.Center;
            //GridView_ShowPlanBody.Columns.Add(bField);

            //bField = new BoundField();
            //bField.DataField = "WeekTwo_EdDate";
            //bField.HeaderText = "第二周结束日期";
            //bField.ReadOnly = true;
            //bField.HeaderStyle.HorizontalAlign = HorizontalAlign.Center;
            //bField.HeaderStyle.Font.Size = 8;
            //bField.ItemStyle.HorizontalAlign = HorizontalAlign.Center;
            //GridView_ShowPlanBody.Columns.Add(bField);

            //[6]
            bField = new BoundField();
            bField.DataField = "WeekTwo_ShopPlan";
            bField.HtmlEncode = false;
            bField.HeaderText = "第二旬计划目标<br />（元）";
            bField.ReadOnly = UserInZB;
            bField.HeaderStyle.HorizontalAlign = HorizontalAlign.Center;
            bField.HeaderStyle.Font.Size = 8;
            bField.ItemStyle.HorizontalAlign = HorizontalAlign.Right;
            GridView_ShowPlanBody.Columns.Add(bField);

            //bField = new BoundField();
            //bField.DataField = "WeekThree_BgDate";
            //bField.HeaderText = "第三周开始日期";
            //bField.ReadOnly = true;
            //bField.HeaderStyle.HorizontalAlign = HorizontalAlign.Center;
            //bField.HeaderStyle.Font.Size = 8;
            //bField.ItemStyle.HorizontalAlign = HorizontalAlign.Center;
            //GridView_ShowPlanBody.Columns.Add(bField);

            //bField = new BoundField();
            //bField.DataField = "WeekThree_EdDate";
            //bField.HeaderText = "第三周结束日期";
            //bField.ReadOnly = true;
            //bField.HeaderStyle.HorizontalAlign = HorizontalAlign.Center;
            //bField.HeaderStyle.Font.Size = 8;
            //bField.ItemStyle.HorizontalAlign = HorizontalAlign.Center;
            //GridView_ShowPlanBody.Columns.Add(bField);

            //[7]
            bField = new BoundField();
            bField.DataField = "WeekThree_ShopPlan";
            bField.HtmlEncode = false;
            bField.HeaderText = "第三旬计划目标<br />（元）";
            bField.ReadOnly = UserInZB;
            bField.HeaderStyle.HorizontalAlign = HorizontalAlign.Center;
            bField.HeaderStyle.Font.Size = 8;
            bField.ItemStyle.HorizontalAlign = HorizontalAlign.Right;
            GridView_ShowPlanBody.Columns.Add(bField);

            //bField = new BoundField();
            //bField.DataField = "WeekFour_BgDate";
            //bField.HeaderText = "第四周开始日期";
            //bField.ReadOnly = true;
            //bField.HeaderStyle.HorizontalAlign = HorizontalAlign.Center;
            //bField.HeaderStyle.Font.Size = 8;
            //bField.ItemStyle.HorizontalAlign = HorizontalAlign.Center;
            //GridView_ShowPlanBody.Columns.Add(bField);

            //bField = new BoundField();
            //bField.DataField = "WeekFour_EdDate";
            //bField.HeaderText = "第四周结束日期";
            //bField.ReadOnly = true;
            //bField.HeaderStyle.HorizontalAlign = HorizontalAlign.Center;
            //bField.HeaderStyle.Font.Size = 8;
            //bField.ItemStyle.HorizontalAlign = HorizontalAlign.Center;
            //GridView_ShowPlanBody.Columns.Add(bField);

            //[8]
            //bField = new BoundField();
            //bField.DataField = "WeekFour_ShopPlan";
            //bField.HtmlEncode = false;
            //bField.HeaderText = "第四周计划目标<br />（万元）";
            //bField.ReadOnly = UserInZB;
            //bField.HeaderStyle.HorizontalAlign = HorizontalAlign.Center;
            //bField.HeaderStyle.Font.Size = 8;
            //bField.ItemStyle.HorizontalAlign = HorizontalAlign.Right;
            //GridView_ShowPlanBody.Columns.Add(bField);

            //[9]
            bField = new BoundField();
            bField.DataField = "WeekDateRemark";
            bField.HeaderText = "日期备注";
            bField.ReadOnly = !UserInZB;
            bField.HeaderStyle.HorizontalAlign = HorizontalAlign.Center;
            bField.HeaderStyle.Font.Size = 8;
            bField.ItemStyle.HorizontalAlign = HorizontalAlign.Center;
            GridView_ShowPlanBody.Columns.Add(bField);

            CommandField cfEdit = new CommandField();
            cfEdit.ButtonType = ButtonType.Link;
            cfEdit.EditText = "编辑";
            cfEdit.ShowEditButton = true;
            cfEdit.UpdateText = "更新";
            cfEdit.CancelText = "取消";
            //cfEdit.DeleteText = "删除";
            cfEdit.ItemStyle.Wrap = false;
            GridView_ShowPlanBody.Columns.Add(cfEdit);

            GridView_ShowPlanBody.DataBind();
        }

        protected void GridView_Body_RowEditing(object sender, GridViewEditEventArgs e)
        {
            //编辑状态的行样式
            //if (!UserInZB)
            //{
            //    GridView_ShowPlanBody.EditRowStyle.Width = 40;
            //    ((TextBox)GridView_ShowPlanBody.Rows[e.NewEditIndex].Cells[5].Controls[0]).Width = 40;
            //}
            

            //Set the edit index.
            GridView_ShowPlanBody.EditIndex = e.NewEditIndex;

            //RetReason的List形式
            //参考资料
            //http://blog.sina.com.cn/s/blog_492faa8f0100di5x.html
            //http://msdn.microsoft.com/en-us/library/aa479353.aspx
            //http://msdn.microsoft.com/zh-cn/library/ms178294(v=vs.100).aspx

            //Bind data to the GridView control. 
            //必须
            OrgCode = DropDownList_SelectOrgCode.SelectedValue;
            GridView_BodyDataBind(OrgType, OrgCode, LastInsDatetime);
        }

        protected void GridView_Body_RowCancelingEdit(object sender, GridViewCancelEditEventArgs e)
        {
            //不是必须赋值
            //如用户删除TextBox内文本，会造成ClsCode = ""。
            //OrgCode = DropDownList_SelectOrgCode.SelectedValue;
            //ClsCode = TextBox_InputClsCode.Text;
            //PluCode = TextBox_InputPluCode.Text;

            //Reset the edit index.
            GridView_ShowPlanBody.EditIndex = -1;

            //Bind data to the GridView control.
            OrgCode = DropDownList_SelectOrgCode.SelectedValue;
            GridView_BodyDataBind(OrgType, OrgCode, LastInsDatetime);
        }

        protected void GridView_Body_RowUpdating(object sender, GridViewUpdateEventArgs e)
        {
            String id = GridView_ShowPlanBody.DataKeys[e.RowIndex]["id"].ToString();

            //取值
            if (UserInZB)
            {
                String WeekDateRemark = ((TextBox)GridView_ShowPlanBody.Rows[e.RowIndex].Cells[9].Controls[0]).Text;
                GridView_ShowPlanBody_RowUpdate(true, id, null, null, null, null, WeekDateRemark);
            }
            else
            {
                String WeekOne_ShopPlan = ((TextBox)GridView_ShowPlanBody.Rows[e.RowIndex].Cells[5].Controls[0]).Text;
                String WeekTwo_ShopPlan = ((TextBox)GridView_ShowPlanBody.Rows[e.RowIndex].Cells[6].Controls[0]).Text;
                String WeekThree_ShopPlan = ((TextBox)GridView_ShowPlanBody.Rows[e.RowIndex].Cells[7].Controls[0]).Text;
                String WeekFour_ShopPlan = ((TextBox)GridView_ShowPlanBody.Rows[e.RowIndex].Cells[8].Controls[0]).Text;
                //String NdCount = ((TextBox)GridView_ShowPlanBody.Rows[e.RowIndex].Cells[8].Controls[0]).Text;
                GridView_ShowPlanBody_RowUpdate(false, id, WeekOne_ShopPlan, WeekTwo_ShopPlan, WeekThree_ShopPlan, WeekFour_ShopPlan, null);
            }

            //Reset the edit index.
            GridView_ShowPlanBody.EditIndex = -1;

            OrgCode = DropDownList_SelectOrgCode.SelectedValue;
            GridView_BodyDataBind(OrgType, OrgCode, LastInsDatetime);
        }

        protected void GridView_ShowPlanBody_RowUpdate(Boolean UserInZB, String id, String WeekOne_ShopPlan, String WeekTwo_ShopPlan, String WeekThree_ShopPlan, String WeekFour_ShopPlan, String WeekDateRemark)
        {
            String UpdateCommandStr = "";

            SqlConnection SqlConnection = new SqlConnection("Data Source=192.168.0.241;Initial Catalog=cls_forms_db;Persist Security Info=True;User ID=sa;Password=My.SA");
            SqlConnection.Open();

            SqlCommand UpdateCommand = new SqlCommand();

            if (UserInZB)
            {
                UpdateCommandStr = "UPDATE [dbo].[u_cls_Saleplan_Table] SET [WeekDateRemark] = @WeekDateRemark WHERE [Id] = @Id";
                UpdateCommand.CommandText = UpdateCommandStr;
                UpdateCommand.CommandType = CommandType.Text;
                UpdateCommand.Connection = SqlConnection;

                //必须转换String到Guid
                //Exception Details: System.InvalidCastException: Invalid cast from 'System.String' to 'System.Guid'.
                UpdateCommand.Parameters.Add("@id", SqlDbType.UniqueIdentifier).Value = new System.Guid(id);
                UpdateCommand.Parameters.Add("@WeekDateRemark", SqlDbType.NVarChar, 4000).Value = WeekDateRemark;

                UpdateCommand.ExecuteNonQuery();
            }
            else
            {
                UpdateCommandStr = "UPDATE [dbo].[u_cls_Saleplan_Table] SET [WeekOne_ShopPlan] = @WeekOne_ShopPlan,[WeekTwo_ShopPlan] = @WeekTwo_ShopPlan,[WeekThree_ShopPlan] = @WeekThree_ShopPlan,[WeekFour_ShopPlan] = @WeekFour_ShopPlan WHERE [Id]=@Id";
                UpdateCommand.CommandText = UpdateCommandStr;
                UpdateCommand.CommandType = CommandType.Text;
                UpdateCommand.Connection = SqlConnection;

                UpdateCommand.Parameters.Add("@id", SqlDbType.UniqueIdentifier).Value = new System.Guid(id);

                //Int32.Parse()导致[FormatException: Input string was not in a correct format.]
                UpdateCommand.Parameters.Add("@WeekOne_ShopPlan", SqlDbType.Money).Value = Decimal.Parse(WeekOne_ShopPlan);
                UpdateCommand.Parameters.Add("@WeekTwo_ShopPlan", SqlDbType.Money).Value = Decimal.Parse(WeekTwo_ShopPlan);
                UpdateCommand.Parameters.Add("@WeekThree_ShopPlan", SqlDbType.Money).Value = Decimal.Parse(WeekThree_ShopPlan);
                UpdateCommand.Parameters.Add("@WeekFour_ShopPlan", SqlDbType.Money).Value = Decimal.Parse(WeekFour_ShopPlan);
                //UpdateCommand.Parameters.Add("@NdCount", SqlDbType.Int);
                //UpdateCommand.Parameters.Add("@EmpNames", SqlDbType.NVarChar, 4000);
                //UpdateCommand.Parameters.Add("@LastModifyDatetime", SqlDbType.DateTime);

                //UpdateCommand.Parameters["@id"].Value = new System.Guid(id);
                //UpdateCommand.Parameters["@ExiCount"].Value = Int32.Parse(ExiCount);
                //UpdateCommand.Parameters["@DepCount"].Value = Int32.Parse(DepCount);
                //UpdateCommand.Parameters["@NcCount"].Value = Int32.Parse(NcCount);
                //UpdateCommand.Parameters["@NtCount"].Value = Int32.Parse(NtCount);
                //UpdateCommand.Parameters["@NdCount"].Value = Int32.Parse(NdCount);
                //UpdateCommand.Parameters["@EmpNames"].Value = EmpNames;
                //UpdateCommand.Parameters["@LastModifyDatetime"].Value = LastModifyDatetime;
            }

            try
            {
                UpdateCommand.ExecuteNonQuery();
            }
            catch (SqlException se)
            {
                //Label_ErrorMsg.Text = se.Message;
                //Label_ErrorMsg.Visible = true;
            }

            UpdateCommand.Dispose();
            SqlConnection.Close();
        }

        protected void Button_ShowPlanBody_Click(object sender, EventArgs e)
        {
            //计划月份条件
            //SelectedMonth = DropDownList_SelectPlanMonth.SelectedValue;

            GridView_ShowPlanBody.EmptyDataText = "您的查询条件没有结果。";

            OrgCode = DropDownList_SelectOrgCode.SelectedValue;

            GridView_BodyDataBind(OrgType, OrgCode, LastInsDatetime);
        }

        protected void Button_Return_Click(object sender, EventArgs e)
        {
            Response.Redirect("SaleplanMgr.aspx");
        }

        protected void Button_Export_Click(object sender, EventArgs e)
        {
            //文件名只能是.xls，.xlsx会导致文件无法打开（Office 2013 X64）
            ExportToExcel(null, GridView_ShowPlanBody, UserInOrgCode + "_" + UserName + "_" + DateTime.Now + "_导出.xls");
        }

        //参考资料
        //http://mattberseth.com/blog/2007/04/export_gridview_to_excel_1.html
        //http://www.c-sharpcorner.com/UploadFile/DipalChoksi/exportxl_asp22_dc04022007235740PM/exportxl_asp22_dc.aspx?ArticleID=1195fe8c-c6f5-4d52-925b-953f4b9de344
        private void ExportToExcel(string FileType, GridView SourceGV, string FileName)
        {
            //参考资料
            //http://mattberseth.com/blog/2007/04/export_gridview_to_excel_1.html
            //http://www.cnblogs.com/sparon/archive/2007/04/06/702946.html
            //
            //Office 2007 File Format MIME Types for HTTP Content Streaming
            //http://blogs.msdn.com/b/vsofficedeveloper/archive/2008/05/08/office-2007-open-xml-mime-types.aspx

            HttpContext.Current.Response.Clear();
            HttpContext.Current.Response.AddHeader(
                "content-disposition", string.Format("attachment; filename={0}", FileName));

            //HttpContext.Current.Response.ContentType = "application/ms-excel";
            HttpContext.Current.Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";

            using (StringWriter sw = new StringWriter())
            {
                using (HtmlTextWriter htw = new HtmlTextWriter(sw))
                {
                    //  Create a table to contain the grid
                    Table table = new Table();

                    //  include the gridline settings
                    table.GridLines = SourceGV.GridLines;

                    //  add the header row to the table
                    if (SourceGV.HeaderRow != null)
                    {
                        ShowSaleplan.PrepareControlForExport(SourceGV.HeaderRow);
                        table.Rows.Add(SourceGV.HeaderRow);
                    }

                    //  add each of the data rows to the table
                    foreach (GridViewRow row in SourceGV.Rows)
                    {
                        ShowSaleplan.PrepareControlForExport(row);
                        table.Rows.Add(row);
                    }

                    //  add the footer row to the table
                    if (SourceGV.FooterRow != null)
                    {
                        ShowSaleplan.PrepareControlForExport(SourceGV.FooterRow);
                        table.Rows.Add(SourceGV.FooterRow);
                    }

                    //  render the table into the htmlwriter
                    table.RenderControl(htw);

                    //  render the htmlwriter into the response
                    HttpContext.Current.Response.Write(sw.ToString());
                    HttpContext.Current.Response.End();
                }
            }
        }

        private static void PrepareControlForExport(Control control)
        {
            for (int i = 0; i < control.Controls.Count; i++)
            {
                Control current = control.Controls[i];
                if (current is LinkButton)
                {
                    control.Controls.Remove(current);
                    control.Controls.AddAt(i, new LiteralControl((current as LinkButton).Text));
                }
                else if (current is ImageButton)
                {
                    control.Controls.Remove(current);
                    control.Controls.AddAt(i, new LiteralControl((current as ImageButton).AlternateText));
                }
                else if (current is HyperLink)
                {
                    control.Controls.Remove(current);
                    control.Controls.AddAt(i, new LiteralControl((current as HyperLink).Text));
                }
                else if (current is DropDownList)
                {
                    control.Controls.Remove(current);
                    control.Controls.AddAt(i, new LiteralControl((current as DropDownList).SelectedItem.Text));
                }
                else if (current is CheckBox)
                {
                    control.Controls.Remove(current);
                    control.Controls.AddAt(i, new LiteralControl((current as CheckBox).Checked ? "True" : "False"));
                }

                if (current.HasControls())
                {
                    ShowSaleplan.PrepareControlForExport(current);
                }
            }
        }

        public override void VerifyRenderingInServerForm(Control control)
        { }
    }
}